﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Model;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace Controller
{
    public class KPIAssignDetailController : Controller.dbConnection.ConnectionClass
    {
        public string Save(KPIAssignDetail KPIs)
        {            
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "UP_InsertUpdateKPIAssignDetail";
            command.Parameters.Add("@ID", SqlDbType.VarChar).Value = KPIs.ID;
            command.Parameters.Add("@KPIID", SqlDbType.VarChar).Value = KPIs.KPI.ID;
            command.Parameters.Add("@KPIASSIGNID", SqlDbType.VarChar).Value = KPIs.KPIAssign.ID;
            command.Parameters.Add("@EMPLOYEEID", SqlDbType.VarChar).Value = KPIs.Employee.ID;
            command.Parameters.Add("@ISACTIVE", SqlDbType.VarChar).Value = KPIs.isActive;
            command.Parameters.Add("@POLARIZATION", SqlDbType.VarChar).Value = KPIs.Polarization;
            command.Parameters.Add("@UOMID", SqlDbType.VarChar).Value = KPIs.UoM == null ? 0 : KPIs.UoM.ID;
            command.Parameters.Add("@UOMDETAILMAXID", SqlDbType.VarChar).Value = KPIs.UoMDetailMax == null ? null : KPIs.UoMDetailMax.ID.ToString();
            command.Parameters.Add("@UOMDETAILMINID", SqlDbType.VarChar).Value = KPIs.UoMDetailMin == null ? null : KPIs.UoMDetailMin.ID.ToString();
            command.Parameters.Add("@MANUALCALCULATION", SqlDbType.VarChar).Value = KPIs.ManualCalculation;
            command.Parameters.Add("@WEIGHT", SqlDbType.VarChar).Value = KPIs.Weight;
            command.Parameters.Add("@WEIGHTENABLEFLAG", SqlDbType.VarChar).Value = KPIs.WeightEnableFlag;
            command.Parameters.Add("@UOMENABLEFLAG", SqlDbType.VarChar).Value = KPIs.UoMEnableFlag;
            command.Parameters.Add("@LASTMODIFIEDBY", SqlDbType.VarChar).Value = KPIs.LastModifiedBy;
            command.Parameters.Add("@CREATEBY", SqlDbType.VarChar).Value = KPIs.CreateBy;
            command.Parameters.Add("@MANDATORY", SqlDbType.VarChar).Value = KPIs.Mandatory;
            command.Parameters.Add("@ASSIGNBY", SqlDbType.VarChar).Value = KPIs.AssignBy;

            return ExecuteNonQuery(command);
        }

        public bool Save(KPIAssign KpiAssign,KPIAssignDetail KpiAssignDtl,Employee EmployeeAssign)
        {
            bool result = true;

            string connectionString = ConfigurationManager.ConnectionStrings["connstring"].ToString();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlTransaction txn = conn.BeginTransaction();
                try
                {
                    //foreach (KPI KPIs in list)
                    //{

                        string query = string.Empty;
                        SqlCommand command = new SqlCommand(query, conn);
                        int KpiAssignID = 0;

                        if (KpiAssign != null)
                        {
                            query = "UP_InsertUpdateKPIAssign";
                            command = new SqlCommand(query, conn);
                            command.CommandType = CommandType.StoredProcedure;
                            command.Parameters.Add("@ID", SqlDbType.VarChar).Value = KpiAssign.ID;
                            command.Parameters.Add("@KPIID", SqlDbType.VarChar).Value = KpiAssign.KPI.ID;
                            command.Parameters.Add("@RGMID", SqlDbType.VarChar).Value = KpiAssign.RGM.ID;
                            command.Parameters.Add("@LOCATIONID", SqlDbType.VarChar).Value = KpiAssign.Location == null ? 0 : KpiAssign.Location.ID;
                            command.Parameters.Add("@SUBBRANCHID", SqlDbType.VarChar).Value = KpiAssign.SubBranch == null ? 0 : KpiAssign.SubBranch.ID;
                            //command.Parameters.Add("@VERIFIER", SqlDbType.VarChar).Value = KPIs.Verifier.Supervisor.ID;
                            command.Parameters.Add("@ISACTIVE", SqlDbType.VarChar).Value = KpiAssign.isActive;
                            command.Parameters.Add("@POLARIZATION", SqlDbType.VarChar).Value = KpiAssign.Polarization;
                            command.Parameters.Add("@UOMID", SqlDbType.VarChar).Value = KpiAssign.UoM == null ? 0 : KpiAssign.UoM.ID;
                            command.Parameters.Add("@UOMDETAILMAXID", SqlDbType.VarChar).Value = KpiAssign.UoMDetailMax == null ? null : KpiAssign.UoMDetailMax.ID.ToString();
                            command.Parameters.Add("@UOMDETAILMINID", SqlDbType.VarChar).Value = KpiAssign.UoMDetailMin == null ? null : KpiAssign.UoMDetailMin.ID.ToString();
                            command.Parameters.Add("@MANUALCALCULATION", SqlDbType.VarChar).Value = KpiAssign.ManualCalculation;
                            command.Parameters.Add("@WEIGHT", SqlDbType.VarChar).Value = KpiAssign.Weight;
                            command.Parameters.Add("@WEIGHTENABLEFLAG", SqlDbType.VarChar).Value = KpiAssign.WeightEnableFlag;
                            command.Parameters.Add("@UOMENABLEFLAG", SqlDbType.VarChar).Value = KpiAssign.UoMEnableFlag;
                            command.Parameters.Add("@LASTMODIFIEDBY", SqlDbType.VarChar).Value = KpiAssign.LastModifiedBy;
                            command.Parameters.Add("@CREATEBY", SqlDbType.VarChar).Value = KpiAssign.CreateBy;

                            command.Transaction = txn;
                            command.ExecuteNonQuery();
                            command.Dispose();

                            query = "SP_GetKpiAssignID_By_CreateBy_CreateDate";
                            command = new SqlCommand(query, conn);
                            command.CommandType = CommandType.StoredProcedure;
                            command.Parameters.Add("@CreateBy", SqlDbType.VarChar).Value = KpiAssign.CreateBy;
                            command.Parameters.Add("@CreateDate", SqlDbType.VarChar).Value = DateTime.Now.ToString("dd/MM/yyyy");
                            object objKpiAssignID = command.ExecuteScalar();
                            KpiAssignID = objKpiAssignID == DBNull.Value ? 0 : Convert.ToInt32(objKpiAssignID);
                            command.Dispose();
                        }




                        query = "UP_InsertUpdateKPIAssignDetail";
                        command = new SqlCommand(query, conn);
                        command.CommandType = CommandType.StoredProcedure; 
                        //command.CommandText = "UP_InsertUpdateKPIAssignDetail";
                        command.Parameters.Add("@ID", SqlDbType.VarChar).Value = KpiAssignDtl.ID;
                        command.Parameters.Add("@KPIID", SqlDbType.VarChar).Value = KpiAssign == null ? KpiAssignDtl.KPIAssign.KPI.ID : KpiAssign.KPI.ID;
                        command.Parameters.Add("@KPIASSIGNID", SqlDbType.VarChar).Value = KpiAssign == null ? KpiAssignDtl.KPIAssign.ID : KpiAssignID;
                        command.Parameters.Add("@EMPLOYEEID", SqlDbType.VarChar).Value = KpiAssignDtl.Employee.ID;
                        command.Parameters.Add("@ISACTIVE", SqlDbType.VarChar).Value = KpiAssignDtl.isActive;
                        command.Parameters.Add("@POLARIZATION", SqlDbType.VarChar).Value = KpiAssignDtl.Polarization;
                        command.Parameters.Add("@UOMID", SqlDbType.VarChar).Value = KpiAssignDtl.UoM == null ? 0 : KpiAssignDtl.UoM.ID;
                        command.Parameters.Add("@UOMDETAILMAXID", SqlDbType.VarChar).Value = KpiAssignDtl.UoMDetailMax == null ? null : KpiAssignDtl.UoMDetailMax.ID.ToString();
                        command.Parameters.Add("@UOMDETAILMINID", SqlDbType.VarChar).Value = KpiAssignDtl.UoMDetailMin == null ? null : KpiAssignDtl.UoMDetailMin.ID.ToString();
                        command.Parameters.Add("@MANUALCALCULATION", SqlDbType.VarChar).Value = KpiAssignDtl.ManualCalculation;
                        command.Parameters.Add("@WEIGHT", SqlDbType.VarChar).Value = KpiAssignDtl.Weight;
                        command.Parameters.Add("@WEIGHTENABLEFLAG", SqlDbType.VarChar).Value = KpiAssignDtl.WeightEnableFlag;
                        command.Parameters.Add("@UOMENABLEFLAG", SqlDbType.VarChar).Value = KpiAssignDtl.UoMEnableFlag;
                        command.Parameters.Add("@LASTMODIFIEDBY", SqlDbType.VarChar).Value = KpiAssignDtl.LastModifiedBy;
                        command.Parameters.Add("@CREATEBY", SqlDbType.VarChar).Value = KpiAssignDtl.CreateBy;
                        command.Parameters.Add("@MANDATORY", SqlDbType.VarChar).Value = KpiAssignDtl.Mandatory;
                        command.Parameters.Add("@ASSIGNBY", SqlDbType.VarChar).Value = KpiAssignDtl.AssignBy;


                        command.Transaction = txn;
                        command.ExecuteNonQuery();
                        command.Dispose();


                        query = "UP_UpdateEmployeeVerifierKPI";
                        command = new SqlCommand(query, conn);
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.Add("@ID", SqlDbType.VarChar).Value = KpiAssign == null ? KpiAssignDtl.KPIAssign.KPI.ID : KpiAssign.KPI.ID;
                        command.Parameters.Add("@EMPLOYEEIDVERIFIER", SqlDbType.VarChar).Value = EmployeeAssign.Supervisor;
                        command.Parameters.Add("@LASTMODIFIEDBY", SqlDbType.VarChar).Value = KpiAssignDtl.LastModifiedBy;
                       


                        command.Transaction = txn;
                        command.ExecuteNonQuery();
                        command.Dispose();

                    //}

                    result = true;
                    txn.Commit();
                }
                catch (Exception ex)
                {
                    txn.Rollback();
                    throw (ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }
            }
            return result;





            //return result;
        }




        public string Delete(KPIAssignDetail KPIs)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "UP_DeleteKPIAssignDetail";
            command.Parameters.Add("@ID", SqlDbType.VarChar).Value = KPIs.ID;
            command.Parameters.Add("@LASTMODIFIEDBY", SqlDbType.VarChar).Value = KPIs.LastModifiedBy;

            return ExecuteNonQuery(command);
        }

        public string UnSignKPI(int KpiID,int EmployeeID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "UP_UnSignKPIAssignDetail";
            command.Parameters.Add("@KPIID", SqlDbType.VarChar).Value = KpiID;
            command.Parameters.Add("@EMPLOYEEID", SqlDbType.VarChar).Value = EmployeeID;

            return ExecuteNonQuery(command);
        }

        public string UnSignKPIMandatory(int KpiID, int EmployeeID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "UP_UnSignKPIAssignDetail_Mandatory";
            command.Parameters.Add("@KPIID", SqlDbType.VarChar).Value = KpiID;
            command.Parameters.Add("@EMPLOYEEID", SqlDbType.VarChar).Value = EmployeeID;

            return ExecuteNonQuery(command);
        }

        public string SignKPI(int KpiID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "UP_SignKPIAssignDetail";
            command.Parameters.Add("@KPIID", SqlDbType.VarChar).Value = KpiID;
            //command.Parameters.Add("@EMPLOYEEID", SqlDbType.VarChar).Value = EmployeeID;

            return ExecuteNonQuery(command);
        }

        public bool BeforeUnSignKPI(int EmployeeIDAssign, int userID)
        {
            bool result = false;
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "KPI_AssignDetailBeforeUnsign";
            command.Parameters.Add("@USERID", SqlDbType.VarChar).Value = userID;

            DataTable dt = GetTable(command).Tables[0];

            foreach (DataRow row in dt.Rows)
            {
                if (EmployeeIDAssign == Convert.ToInt32(row[0]))
                    result = true;
                //break;
            }

          

            //return ExecuteNonQuery(command);
            return result;
        }


        public string BeforeAssignKPI(int KpiID,int EmployeeID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "SP_CheckBeforeAssignKPI";
            command.Parameters.Add("@KPIID", SqlDbType.Int).Value = KpiID;
            command.Parameters.Add("@EMPLOYEEID", SqlDbType.Int).Value = EmployeeID;


            return GetSingleData(command);
        }


        public IList<KPIAssignDetail> GetAllByUserID(string sSearch, int CurrentUser, int PageNumber, int Datalength, string SortCol, string SortDirection, out int TotalData, int year)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "SP_GetAllKPIAssignDetailsByUserID";
            command.Parameters.Add("@SEARCH", SqlDbType.VarChar).Value = sSearch;
            command.Parameters.Add("@USERID", SqlDbType.VarChar).Value = CurrentUser;
            command.Parameters.Add("@YEAR", SqlDbType.VarChar).Value = year;

            DataSet dx = GetTable(command);

            List<KPIAssignDetail> KPIs = new List<KPIAssignDetail>();

            switch (SortDirection)
            {
                case "asc":
                    switch (SortCol)
                    {
                        case "KPIDescription":
                            dx.Tables[0].DefaultView.Sort = "KPIID ASC";
                            break;
                        case "AssignedTo":
                            dx.Tables[0].DefaultView.Sort = "EmployeeID ASC";
                            break;
                        case "isActive":
                            dx.Tables[0].DefaultView.Sort = "isActive ASC";
                            break;
                        case "Periode":
                            dx.Tables[0].DefaultView.Sort = "Periode ASC";
                            break;
                        case "Year":
                            dx.Tables[0].DefaultView.Sort = "Year ASC";
                            break;
                        case "UoMMax":
                            dx.Tables[0].DefaultView.Sort = "UoMDetailIDMaxDesc ASC";
                            break;
                        case "UoMMin":
                            dx.Tables[0].DefaultView.Sort = "UoMDetailIDMinDesc ASC";
                            break;
                        case "ScoredBySystem":
                            dx.Tables[0].DefaultView.Sort = "ScoredBySystem ASC";
                            break;
                        case "Weight":
                            dx.Tables[0].DefaultView.Sort = "Weight ASC";
                            break;
                       
                    }
                    break;
                case "desc":
                    switch (SortCol)
                    {
                        case "KPIDescription":
                            dx.Tables[0].DefaultView.Sort = "KPIID DESC";
                            break;
                        case "AssignedTo":
                            dx.Tables[0].DefaultView.Sort = "EmployeeID DESC";
                            break;
                        case "isActive":
                            dx.Tables[0].DefaultView.Sort = "isActive DESC";
                            break;
                        case "Periode":
                            dx.Tables[0].DefaultView.Sort = "Periode DESC";
                            break;
                        case "Year":
                            dx.Tables[0].DefaultView.Sort = "Year DESC";
                            break;
                        case "UoMMax":
                            dx.Tables[0].DefaultView.Sort = "UoMDetailIDMaxDesc DESC";
                            break;
                        case "UoMMin":
                            dx.Tables[0].DefaultView.Sort = "UoMDetailIDMinDesc DESC";
                            break;
                        case "ScoredBySystem":
                            dx.Tables[0].DefaultView.Sort = "ScoredBySystem DESC";
                            break;
                        case "Weight":
                            dx.Tables[0].DefaultView.Sort = "Weight DESC";
                            break;
                    }
                    break;
            }

            DataTable dt = dx.Tables[0].DefaultView.ToTable();

            if (dt.Rows.Count > 0)
            {
                for (int i = PageNumber; i < Datalength; i++)
                {
                    if (i < dt.Rows.Count)
                    {
                        KPIAssignDetail p = new KPIAssignDetail();
                        p.ID = dt.Rows[i]["ID"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["ID"].ToString());
                        //p.KPI = new KPIController().GetKPIByID(Convert.ToInt16(dt.Rows[i]["KPIID"].ToString()));
                        //p.KPIAssign = new KPIAssignController().GetKPIAssignByID(Convert.ToInt16(dt.Rows[i]["KPIAssignID"].ToString()));
                        //p.Employee = new EmployeeController().GetEmployeeByID(dx.Tables[0].Rows[i]["EmployeeID"] == DBNull.Value ? 0 : Convert.ToInt16(dx.Tables[0].Rows[i]["EmployeeID"].ToString()));

                        p.KPI = new KPI();
                        p.KPI.Year = dt.Rows[i]["Year"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["Year"].ToString());
                        p.KPI.Periode = dt.Rows[i]["Periode"] == DBNull.Value ? string.Empty : dt.Rows[i]["Periode"].ToString();
                        p.KPI.ID = dt.Rows[i]["KPIID"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["KPIID"].ToString());
                        p.KPI.ScoredBySystem = dt.Rows[i]["ScoredBySystem"] == DBNull.Value ? false : Convert.ToBoolean(dt.Rows[i]["ScoredBySystem"].ToString());
                        p.KPI.Description = dt.Rows[i]["Description"] == DBNull.Value ? string.Empty : dt.Rows[i]["Description"].ToString();

                        p.Weight = dt.Rows[i]["Weight"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["Weight"].ToString());
                        
                        //p.UoM = new UoMController().GetUoMByID(dt.Rows[i]["UoMID"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["UoMID"].ToString()));
                        //p.UoMDetailMax = new UoMDetailController().GetUoMDetailByID(dt.Rows[i]["UoMDetailIDMax"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["UoMDetailIDMax"].ToString()));
                        //p.UoMDetailMin = new UoMDetailController().GetUoMDetailByID(dt.Rows[i]["UoMDetailIDMin"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["UoMDetailIDMin"].ToString()));

                        p.UoM = new UoM();
                        p.UoM.ID = dt.Rows[i]["UoMID"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["UoMID"].ToString());
                        p.UoM.Description = dt.Rows[i]["UoMIDDesc"] == DBNull.Value ? string.Empty : dt.Rows[i]["UoMIDDesc"].ToString();

                        p.UoMDetailMax = new UoMDetail();
                        p.UoMDetailMax.ID = dt.Rows[i]["UoMDetailIDMax"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["UoMDetailIDMax"].ToString());
                        p.UoMDetailMax.Description = dt.Rows[i]["UoMDetailIDMaxDesc"] == DBNull.Value ? string.Empty : dt.Rows[i]["UoMDetailIDMaxDesc"].ToString();

                        p.UoMDetailMin = new UoMDetail();
                        p.UoMDetailMin.ID = dt.Rows[i]["UoMDetailIDMin"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["UoMDetailIDMin"].ToString());
                        p.UoMDetailMin.Description = dt.Rows[i]["UoMDetailIDMinDesc"] == DBNull.Value ? string.Empty : dt.Rows[i]["UoMDetailIDMinDesc"].ToString();

                        p.WeightEnableFlag = dt.Rows[i]["WeightEnableFlag"] == DBNull.Value ? false : Convert.ToBoolean(dt.Rows[i]["WeightEnableFlag"].ToString());
                        p.UoMEnableFlag = dt.Rows[i]["UoMEnableFlag"] == DBNull.Value ? false : Convert.ToBoolean(dt.Rows[i]["UoMEnableFlag"].ToString());
                        p.Polarization = dt.Rows[i]["Polarization"] == DBNull.Value ? string.Empty : dt.Rows[i]["Polarization"].ToString();
                        p.ManualCalculation = dt.Rows[i]["ManualCalculation"] == DBNull.Value ? 0 : Convert.ToInt32(dt.Rows[i]["ManualCalculation"].ToString());
                        p.isActive = dt.Rows[i]["isActive"] == DBNull.Value ? false : Convert.ToBoolean(dt.Rows[i]["isActive"].ToString());
                        p.LastModifiedBy = dt.Rows[i]["LastModifiedBy"] == DBNull.Value ? string.Empty : dt.Rows[i]["LastModifiedBy"].ToString();
                        p.LastModifiedDate = dt.Rows[i]["LastModifiedDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dt.Rows[i]["LastModifiedDate"].ToString());
                        p.CreateBy = dt.Rows[i]["CreateBy"] == DBNull.Value ? string.Empty : dt.Rows[i]["CreateBy"].ToString();
                        p.CreateDate = dt.Rows[i]["CreateDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dt.Rows[i]["CreateDate"].ToString());



                        p.KPIAssign.ID = dt.Rows[i]["KPIAssignID"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["KPIAssignID"].ToString());
                        p.Employee.ID = dt.Rows[i]["EmployeeID"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["EmployeeID"].ToString());
                        
                        
                        KPIs.Add(p);
                    }
                    else
                    {
                        break;
                    }

                }
            }

            TotalData = dx.Tables[0].Rows.Count;

            return KPIs.ToList();
        }


        public IList<KPIAssignDetail> GetAll(string sSearch, int PageNumber, int Datalength, string SortCol, string SortDirection, out int TotalData, Employee User, int year)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "SP_GetAllKPIAssignDetails";
            command.Parameters.Add("@SEARCH", SqlDbType.VarChar).Value = sSearch;
            command.Parameters.Add("@USERID", SqlDbType.VarChar).Value = User.ID;
            command.Parameters.Add("@YEAR", SqlDbType.VarChar).Value = year;

            DataSet dx = GetTable(command);

            List<KPIAssignDetail> KPIs = new List<KPIAssignDetail>();

            switch (SortDirection)
            {
                case "asc":
                    switch (SortCol)
                    {
                        case "KPIDescription":
                            dx.Tables[0].DefaultView.Sort = "KPIID ASC";
                            break;
                        case "RGM":
                            dx.Tables[0].DefaultView.Sort = "RGMID ASC";
                            break;
                        case "Location":
                            dx.Tables[0].DefaultView.Sort = "LocationID ASC";
                            break;
                        case "SubBranch":
                            dx.Tables[0].DefaultView.Sort = "SubBranchID ASC";
                            break;
                        case "UoM":
                            dx.Tables[0].DefaultView.Sort = "UoMID ASC";
                            break;
                        case "UoMMax":
                            dx.Tables[0].DefaultView.Sort = "UoMDetailIDMax ASC";
                            break;
                        case "UoMMin":
                            dx.Tables[0].DefaultView.Sort = "UoMDetailIDMin ASC";
                            break;
                        case "Employee":
                            dx.Tables[0].DefaultView.Sort = "EmployeeID ASC";
                            break;
                        case "LastModifiedDate":
                            dx.Tables[0].DefaultView.Sort = "LastModifiedDate ASC";
                            break;
                        case "LastModifiedBy":
                            dx.Tables[0].DefaultView.Sort = "LastModifiedBy ASC";
                            break;
                        case "CreateDate":
                            dx.Tables[0].DefaultView.Sort = "CreateDate ASC";
                            break;
                        case "CreateBy":
                            dx.Tables[0].DefaultView.Sort = "CreateBy ASC";
                            break;
                    }
                    break;
                case "desc":
                    switch (SortCol)
                    {
                        case "KPIDescription":
                            dx.Tables[0].DefaultView.Sort = "KPIID DESC";
                            break;
                        case "RGM":
                            dx.Tables[0].DefaultView.Sort = "RGMID DESC";
                            break;
                        case "Location":
                            dx.Tables[0].DefaultView.Sort = "LocationID DESC";
                            break;
                        case "SubBranch":
                            dx.Tables[0].DefaultView.Sort = "SubBranchID DESC";
                            break;
                        case "UoM":
                            dx.Tables[0].DefaultView.Sort = "UoMID DESC";
                            break;
                        case "UoMMax":
                            dx.Tables[0].DefaultView.Sort = "UoMDetailIDMax DESC";
                            break;
                        case "UoMMin":
                            dx.Tables[0].DefaultView.Sort = "UoMDetailIDMin DESC";
                            break;
                        case "Employee":
                            dx.Tables[0].DefaultView.Sort = "EmployeeID DESC";
                            break;
                        case "LastModifiedDate":
                            dx.Tables[0].DefaultView.Sort = "LastModifiedDate DESC";
                            break;
                        case "LastModifiedBy":
                            dx.Tables[0].DefaultView.Sort = "LastModifiedBy DESC";
                            break;
                        case "CreateDate":
                            dx.Tables[0].DefaultView.Sort = "CreateDate DESC";
                            break;
                        case "CreateBy":
                            dx.Tables[0].DefaultView.Sort = "CreateBy DESC";
                            break;
                    }
                    break;
            }

            DataTable dt = dx.Tables[0].DefaultView.ToTable();

            if (dx.Tables[0].Rows.Count > 0)
            {
                for (int i = PageNumber; i < Datalength; i++)
                {
                    if (i < dx.Tables[0].Rows.Count)
                    {
                        KPIAssignDetail p = new KPIAssignDetail();
                        p.ID = Convert.ToInt16(dt.Rows[i]["ID"].ToString());
                        
                        //p.KPI = new KPIController().GetKPIByID(Convert.ToInt16(dt.Rows[i]["KPIID"].ToString()));
                        p.KPI = new KPI();
                        p.KPI.ID = Convert.ToInt16(dt.Rows[i]["KPIID"].ToString());
                        p.KPI.Description = dt.Rows[i]["DescriptionKPI"].ToString();
                        p.KPI.Year = Convert.ToInt16(dt.Rows[i]["Year"].ToString());

                        p.KPI.KPIParent = new KPI();
                        p.KPI.KPIParent.Description = dt.Rows[i]["DescriptionKPIParent"].ToString();

                        p.KPI.GroupKPI = new GroupKPI();
                        p.KPI.GroupKPI.Description = dt.Rows[i]["DescriptionGroup"].ToString();

                        p.KPI.CategoryKPI = new CategoryKPI();
                        p.KPI.CategoryKPI.Description = dt.Rows[i]["DescriptionCategory"].ToString();

                        p.KPI.TypeKPI = new TypeKPI();
                        p.KPI.TypeKPI.Description = dt.Rows[i]["DescriptionType"].ToString();

                        p.KPI.SubTypeKPI = new SubTypeKPI();
                        p.KPI.SubTypeKPI.Description = dt.Rows[i]["DescriptionSubType"].ToString();

                        //p.KPIAssign = new KPIAssignController().GetKPIAssignByID(Convert.ToInt16(dt.Rows[i]["KPIAssignID"].ToString()));
                        p.KPIAssign = new KPIAssign();
                        p.KPIAssign.ID = Convert.ToInt16(dt.Rows[i]["KPIAssignID"].ToString());

                        //p.Employee = new EmployeeController().GetEmployeeByID(dt.Rows[i]["EmployeeID"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["EmployeeID"].ToString()));
                        p.Employee = new Employee();
                        p.Employee.ID = Convert.ToInt16(dt.Rows[i]["EmployeeID"].ToString());
                        p.Employee.FirstName = dt.Rows[i]["FirstName"].ToString();
                        p.Employee.LastName = dt.Rows[i]["LastName"].ToString();

                        p.Weight = Convert.ToInt16(dt.Rows[i]["Weight"].ToString());
                        
                        //p.UoM = new UoMController().GetUoMByID(dt.Rows[i]["UoMID"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["UoMID"].ToString()));
                        //p.UoMDetailMax = new UoMDetailController().GetUoMDetailByID(dt.Rows[i]["UoMDetailIDMax"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["UoMDetailIDMax"].ToString()));
                        //p.UoMDetailMin = new UoMDetailController().GetUoMDetailByID(dt.Rows[i]["UoMDetailIDMin"] == DBNull.Value ? 0 : Convert.ToInt16(dt.Rows[i]["UoMDetailIDMin"].ToString()));


                        p.UoMDetailMax = new UoMDetail();
                        p.UoMDetailMax.Description = dt.Rows[i]["DescriptionUoMDetailMax"].ToString();

                        p.UoMDetailMin = new UoMDetail();
                        p.UoMDetailMin.Description = dt.Rows[i]["DescriptionUoMDetailMin"].ToString();

                        p.WeightEnableFlag = Convert.ToBoolean(dt.Rows[i]["WeightEnableFlag"].ToString());
                        p.UoMEnableFlag = Convert.ToBoolean(dt.Rows[i]["UoMEnableFlag"].ToString());
                        p.Polarization = dt.Rows[i]["Polarization"].ToString();
                        p.ManualCalculation = Convert.ToInt32(dt.Rows[i]["ManualCalculation"].ToString());
                        p.isActive = Convert.ToBoolean(dt.Rows[i]["isActive"].ToString());
                        p.LastModifiedBy = dt.Rows[i]["LastModifiedBy"].ToString();
                        p.LastModifiedDate = dt.Rows[i]["LastModifiedDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dt.Rows[i]["LastModifiedDate"].ToString());
                        p.CreateBy = dt.Rows[i]["CreateBy"].ToString();
                        p.CreateDate = dt.Rows[i]["CreateDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dt.Rows[i]["CreateDate"].ToString());
                        p.Mandatory = Convert.ToBoolean(dt.Rows[i]["Mandatory"]);
                        KPIs.Add(p);
                    }
                    else
                    {
                        break;
                    }

                }
            }

            TotalData = dx.Tables[0].Rows.Count;

            return KPIs.ToList();
        }

        public IList<KPIAssignDetail> GetAllNon(string sSearch, int PageNumber, int Datalength, string SortCol, string SortDirection, out int TotalData, Employee User, int year)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "SP_GetAllKPIAssignDetails_Non";
            command.Parameters.Add("@SEARCH", SqlDbType.VarChar).Value = sSearch;
            command.Parameters.Add("@USERID", SqlDbType.VarChar).Value = User.ID;
            command.Parameters.Add("@YEAR", SqlDbType.VarChar).Value = year;

            DataSet dx = GetTable(command);

            List<KPIAssignDetail> KPIs = new List<KPIAssignDetail>();

            switch (SortDirection)
            {
                case "asc":
                    switch (SortCol)
                    {
                        case "KPIDescription":
                            dx.Tables[0].DefaultView.Sort = "KPIID ASC";
                            break;
                        case "RGM":
                            dx.Tables[0].DefaultView.Sort = "RGMID ASC";
                            break;
                        case "Location":
                            dx.Tables[0].DefaultView.Sort = "LocationID ASC";
                            break;
                        case "SubBranch":
                            dx.Tables[0].DefaultView.Sort = "SubBranchID ASC";
                            break;
                        case "UoM":
                            dx.Tables[0].DefaultView.Sort = "UoMID ASC";
                            break;
                        case "UoMMax":
                            dx.Tables[0].DefaultView.Sort = "UoMDetailIDMax ASC";
                            break;
                        case "UoMMin":
                            dx.Tables[0].DefaultView.Sort = "UoMDetailIDMin ASC";
                            break;
                        case "Employee":
                            dx.Tables[0].DefaultView.Sort = "EmployeeID ASC";
                            break;
                        case "LastModifiedDate":
                            dx.Tables[0].DefaultView.Sort = "LastModifiedDate ASC";
                            break;
                        case "LastModifiedBy":
                            dx.Tables[0].DefaultView.Sort = "LastModifiedBy ASC";
                            break;
                        case "CreateDate":
                            dx.Tables[0].DefaultView.Sort = "CreateDate ASC";
                            break;
                        case "CreateBy":
                            dx.Tables[0].DefaultView.Sort = "CreateBy ASC";
                            break;
                    }
                    break;
                case "desc":
                    switch (SortCol)
                    {
                        case "KPIDescription":
                            dx.Tables[0].DefaultView.Sort = "KPIID DESC";
                            break;
                        case "RGM":
                            dx.Tables[0].DefaultView.Sort = "RGMID DESC";
                            break;
                        case "Location":
                            dx.Tables[0].DefaultView.Sort = "LocationID DESC";
                            break;
                        case "SubBranch":
                            dx.Tables[0].DefaultView.Sort = "SubBranchID DESC";
                            break;
                        case "UoM":
                            dx.Tables[0].DefaultView.Sort = "UoMID DESC";
                            break;
                        case "UoMMax":
                            dx.Tables[0].DefaultView.Sort = "UoMDetailIDMax DESC";
                            break;
                        case "UoMMin":
                            dx.Tables[0].DefaultView.Sort = "UoMDetailIDMin DESC";
                            break;
                        case "Employee":
                            dx.Tables[0].DefaultView.Sort = "EmployeeID DESC";
                            break;
                        case "LastModifiedDate":
                            dx.Tables[0].DefaultView.Sort = "LastModifiedDate DESC";
                            break;
                        case "LastModifiedBy":
                            dx.Tables[0].DefaultView.Sort = "LastModifiedBy DESC";
                            break;
                        case "CreateDate":
                            dx.Tables[0].DefaultView.Sort = "CreateDate DESC";
                            break;
                        case "CreateBy":
                            dx.Tables[0].DefaultView.Sort = "CreateBy DESC";
                            break;
                    }
                    break;
            }

            DataTable dt = dx.Tables[0].DefaultView.ToTable();

            if (dx.Tables[0].Rows.Count > 0)
            {
                for (int i = PageNumber; i < Datalength; i++)
                {
                    if (i < dx.Tables[0].Rows.Count)
                    {
                        KPIAssignDetail p = new KPIAssignDetail();
                        p.ID = Convert.ToInt16(dx.Tables[0].Rows[i]["ID"].ToString());
                        p.KPI = new KPIController().GetKPIByID(Convert.ToInt16(dx.Tables[0].Rows[i]["KPIID"].ToString()));
                        p.KPIAssign = new KPIAssignController().GetKPIAssignByID(Convert.ToInt16(dx.Tables[0].Rows[i]["KPIAssignID"].ToString()));
                        p.Employee = new EmployeeController().GetEmployeeByID(dx.Tables[0].Rows[i]["EmployeeID"] == DBNull.Value ? 0 : Convert.ToInt16(dx.Tables[0].Rows[i]["EmployeeID"].ToString()));
                        p.Weight = Convert.ToInt16(dx.Tables[0].Rows[i]["Weight"].ToString());
                        p.UoM = new UoMController().GetUoMByID(dx.Tables[0].Rows[i]["UoMID"] == DBNull.Value ? 0 : Convert.ToInt16(dx.Tables[0].Rows[i]["UoMID"].ToString()));
                        p.UoMDetailMax = new UoMDetailController().GetUoMDetailByID(dx.Tables[0].Rows[i]["UoMDetailIDMax"] == DBNull.Value ? 0 : Convert.ToInt16(dx.Tables[0].Rows[i]["UoMDetailIDMax"].ToString()));
                        p.UoMDetailMin = new UoMDetailController().GetUoMDetailByID(dx.Tables[0].Rows[i]["UoMDetailIDMin"] == DBNull.Value ? 0 : Convert.ToInt16(dx.Tables[0].Rows[i]["UoMDetailIDMin"].ToString()));
                        p.WeightEnableFlag = Convert.ToBoolean(dx.Tables[0].Rows[i]["WeightEnableFlag"].ToString());
                        p.UoMEnableFlag = Convert.ToBoolean(dx.Tables[0].Rows[i]["UoMEnableFlag"].ToString());
                        p.Polarization = dx.Tables[0].Rows[i]["Polarization"].ToString();
                        p.ManualCalculation = Convert.ToInt32(dx.Tables[0].Rows[i]["ManualCalculation"].ToString());
                        p.isActive = Convert.ToBoolean(dx.Tables[0].Rows[i]["isActive"].ToString());
                        p.LastModifiedBy = dx.Tables[0].Rows[i]["LastModifiedBy"].ToString();
                        p.LastModifiedDate = dx.Tables[0].Rows[i]["LastModifiedDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dx.Tables[0].Rows[i]["LastModifiedDate"].ToString());
                        p.CreateBy = dx.Tables[0].Rows[i]["CreateBy"].ToString();
                        p.CreateDate = dx.Tables[0].Rows[i]["CreateDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dx.Tables[0].Rows[i]["CreateDate"].ToString());
                        KPIs.Add(p);
                    }
                    else
                    {
                        break;
                    }

                }
            }

            TotalData = dx.Tables[0].Rows.Count;

            return KPIs.ToList();
        }

        public KPIAssignDetail GetKPIAssignDetailByID(int ID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "SP_GetKPIAssignDetailByID";
            command.Parameters.Add("@ID", SqlDbType.Int).Value = ID;

            IList<string> Data = GetSingleRow(command);
            KPIAssignDetail KPIs = null;

            if (Data != null && Data.Count > 0)
            {
                KPIs = new KPIAssignDetail();
                KPIs.ID = Convert.ToInt16(Data[0].ToString());
                KPIs.KPI = new KPIController().GetKPIByID(Convert.ToInt16(Data[1].ToString()));
                KPIs.KPIAssign = new KPIAssignController().GetKPIAssignByID(Convert.ToInt16(Data[2].ToString()));
                KPIs.Employee = new EmployeeController().GetEmployeeByID(Data[3] == string.Empty ? 0 : Convert.ToInt16(Data[3].ToString()));
                KPIs.Weight = Convert.ToInt16(Data[4].ToString());
                KPIs.UoM = new UoMController().GetUoMByID(Data[5] == string.Empty ? 0 : Convert.ToInt16(Data[5].ToString()));
                KPIs.UoMDetailMax = new UoMDetailController().GetUoMDetailByID(Data[6] == string.Empty ? 0 : Convert.ToInt16(Data[6].ToString()));
                KPIs.UoMDetailMin = new UoMDetailController().GetUoMDetailByID(Data[7] == string.Empty ? 0 : Convert.ToInt16(Data[7].ToString()));
                KPIs.WeightEnableFlag = Convert.ToBoolean(Data[8].ToString());
                KPIs.UoMEnableFlag = Convert.ToBoolean(Data[9].ToString());
                KPIs.Polarization = Data[10].ToString();
                KPIs.ManualCalculation = Convert.ToInt32(Data[11].ToString());
                KPIs.isActive = Convert.ToBoolean(Data[12].ToString());
                KPIs.LastModifiedDate = Data[13] == string.Empty ? (DateTime?)null : Convert.ToDateTime(Data[13].ToString());
                KPIs.LastModifiedBy = Data[14].ToString();
                KPIs.CreateDate = Data[15] == string.Empty ? (DateTime?)null : Convert.ToDateTime(Data[15].ToString());
                KPIs.CreateBy = Data[16].ToString();
                KPIs.Mandatory = Convert.ToBoolean(Data[17].ToString());
                KPIs.AssignBy = Convert.ToInt16(Data[18].ToString());
            }

            return KPIs;
        }

        public IList<KPIAssignDetail> GetKPIAssignDetailByKPIAssignID(int KPIAssignID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "SP_GetKPIAssignDetailByKPIAssignID";
            command.Parameters.Add("@KPIASSIGNID", SqlDbType.Int).Value = KPIAssignID;

            DataSet dx = GetTable(command);

            List<KPIAssignDetail> KPIs = new List<KPIAssignDetail>();

            DataTable dt = dx.Tables[0].DefaultView.ToTable();

            if (dx.Tables[0].Rows.Count > 0)
            {
                for (int i = 0; i < dx.Tables[0].Rows.Count; i++)
                {
                    if (i < dx.Tables[0].Rows.Count)
                    {
                        KPIAssignDetail p = new KPIAssignDetail();
                        p.ID = Convert.ToInt16(dx.Tables[0].Rows[i]["ID"].ToString());
                        //p.KPI = new KPIController().GetKPIByID(Convert.ToInt16(dx.Tables[0].Rows[i]["KPIID"].ToString()));
                        //p.KPIAssign = new KPIAssignController().GetKPIAssignByID(Convert.ToInt16(dx.Tables[0].Rows[i]["KPIAssignID"].ToString()));
                        //p.Employee = new EmployeeController().GetEmployeeByID(dx.Tables[0].Rows[i]["EmployeeID"] == DBNull.Value ? 0 : Convert.ToInt16(dx.Tables[0].Rows[i]["EmployeeID"].ToString()));
                        p.Weight = Convert.ToInt16(dx.Tables[0].Rows[i]["Weight"].ToString());
                        p.UoM = new UoMController().GetUoMByID(dx.Tables[0].Rows[i]["UoMID"] == DBNull.Value ? 0 : Convert.ToInt16(dx.Tables[0].Rows[i]["UoMID"].ToString()));
                        p.UoMDetailMax = new UoMDetailController().GetUoMDetailByID(dx.Tables[0].Rows[i]["UoMDetailIDMax"] == DBNull.Value ? 0 : Convert.ToInt16(dx.Tables[0].Rows[i]["UoMDetailIDMax"].ToString()));
                        p.UoMDetailMin = new UoMDetailController().GetUoMDetailByID(dx.Tables[0].Rows[i]["UoMDetailIDMin"] == DBNull.Value ? 0 : Convert.ToInt16(dx.Tables[0].Rows[i]["UoMDetailIDMin"].ToString()));
                        p.WeightEnableFlag = Convert.ToBoolean(dx.Tables[0].Rows[i]["WeightEnableFlag"].ToString());
                        p.UoMEnableFlag = Convert.ToBoolean(dx.Tables[0].Rows[i]["UoMEnableFlag"].ToString());
                        p.Polarization = dx.Tables[0].Rows[i]["Polarization"].ToString();
                        p.ManualCalculation = Convert.ToInt32(dx.Tables[0].Rows[i]["ManualCalculation"].ToString());
                        p.isActive = Convert.ToBoolean(dx.Tables[0].Rows[i]["isActive"].ToString());
                        p.LastModifiedBy = dx.Tables[0].Rows[i]["LastModifiedBy"].ToString();
                        p.LastModifiedDate = dx.Tables[0].Rows[i]["LastModifiedDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dx.Tables[0].Rows[i]["LastModifiedDate"].ToString());
                        p.CreateBy = dx.Tables[0].Rows[i]["CreateBy"].ToString();
                        p.CreateDate = dx.Tables[0].Rows[i]["CreateDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dx.Tables[0].Rows[i]["CreateDate"].ToString());

                        p.KPI.ID = Convert.ToInt16(dx.Tables[0].Rows[i]["KPIID"].ToString());
                        p.KPI.Description = dx.Tables[0].Rows[i]["Description"].ToString();
                        p.KPIAssign.ID = Convert.ToInt16(dx.Tables[0].Rows[i]["KPIAssignID"].ToString());
                        p.Employee.ID = Convert.ToInt16(dx.Tables[0].Rows[i]["EmployeeID"].ToString());
                        p.KPI.Description = dx.Tables[0].Rows[i]["Description"].ToString();
                        p.KPIAssign.ID = Convert.ToInt16(dx.Tables[0].Rows[i]["KPIAssignID"].ToString());
                        p.Employee.ID = Convert.ToInt16(dx.Tables[0].Rows[i]["EmployeeID"].ToString());
                        p.Employee.FirstName = dx.Tables[0].Rows[i]["FirstName"].ToString();
                        p.Employee.LastName = dx.Tables[0].Rows[i]["LastName"].ToString();
                        KPIs.Add(p);
                    }
                    else
                    {
                        break;
                    }

                }
            }

            return KPIs.ToList();
        }

        public IList<KPIAssignDetail> GetEmployeeKPIAssignDetailByKPIAssignID(int KPIAssignID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "SP_GetKPIAssignDetailByKPIAssignID";
            command.Parameters.Add("@KPIASSIGNID", SqlDbType.Int).Value = KPIAssignID;

            DataSet dx = GetTable(command);

            List<KPIAssignDetail> KPIs = new List<KPIAssignDetail>();

            DataTable dt = dx.Tables[0].DefaultView.ToTable();

            if (dx.Tables[0].Rows.Count > 0)
            {
                for (int i = 0; i < dx.Tables[0].Rows.Count; i++)
                {
                    if (i < dx.Tables[0].Rows.Count)
                    {
                        KPIAssignDetail p = new KPIAssignDetail();
                        //p.ID = Convert.ToInt16(dx.Tables[0].Rows[i]["ID"].ToString());
                        ///p.KPI = new KPIController().GetKPIByID(Convert.ToInt16(dx.Tables[0].Rows[i]["KPIID"].ToString()));
                        //p.KPIAssign = new KPIAssignController().GetKPIAssignByID(Convert.ToInt16(dx.Tables[0].Rows[i]["KPIAssignID"].ToString()));
                        p.Employee = new EmployeeController().GetEmployeeByID(dx.Tables[0].Rows[i]["EmployeeID"] == DBNull.Value ? 0 : Convert.ToInt16(dx.Tables[0].Rows[i]["EmployeeID"].ToString()));
                        //p.Weight = Convert.ToInt16(dx.Tables[0].Rows[i]["Weight"].ToString());
                        //p.UoM = new UoMController().GetUoMByID(dx.Tables[0].Rows[i]["UoMID"] == DBNull.Value ? 0 : Convert.ToInt16(dx.Tables[0].Rows[i]["UoMID"].ToString()));
                        //p.UoMDetailMax = new UoMDetailController().GetUoMDetailByID(dx.Tables[0].Rows[i]["UoMDetailIDMax"] == DBNull.Value ? 0 : Convert.ToInt16(dx.Tables[0].Rows[i]["UoMDetailIDMax"].ToString()));
                        //p.UoMDetailMin = new UoMDetailController().GetUoMDetailByID(dx.Tables[0].Rows[i]["UoMDetailIDMin"] == DBNull.Value ? 0 : Convert.ToInt16(dx.Tables[0].Rows[i]["UoMDetailIDMin"].ToString()));
                        //p.WeightEnableFlag = Convert.ToBoolean(dx.Tables[0].Rows[i]["WeightEnableFlag"].ToString());
                        //p.UoMEnableFlag = Convert.ToBoolean(dx.Tables[0].Rows[i]["UoMEnableFlag"].ToString());
                        //p.Polarization = dx.Tables[0].Rows[i]["Polarization"].ToString();
                        //p.ManualCalculation = Convert.ToInt32(dx.Tables[0].Rows[i]["ManualCalculation"].ToString());
                        //p.isActive = Convert.ToBoolean(dx.Tables[0].Rows[i]["isActive"].ToString());
                        //p.LastModifiedBy = dx.Tables[0].Rows[i]["LastModifiedBy"].ToString();
                        //p.LastModifiedDate = dx.Tables[0].Rows[i]["LastModifiedDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dx.Tables[0].Rows[i]["LastModifiedDate"].ToString());
                        //p.CreateBy = dx.Tables[0].Rows[i]["CreateBy"].ToString();
                        //p.CreateDate = dx.Tables[0].Rows[i]["CreateDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dx.Tables[0].Rows[i]["CreateDate"].ToString());
                        KPIs.Add(p);
                    }
                    else
                    {
                        break;
                    }

                }
            }

            return KPIs.ToList();
        }

        public IList<KPIAssignDetail> GetAllByYearAndUserID(int CurrentUser, int year)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "SP_GetAllKPIAssignDetailsByYearAndUserID";
            command.Parameters.Add("@YEAR", SqlDbType.VarChar).Value = year;
            command.Parameters.Add("@USERID", SqlDbType.VarChar).Value = CurrentUser;

            DataSet dx = GetTable(command);

            List<KPIAssignDetail> KPIs = new List<KPIAssignDetail>();

            DataTable dt = dx.Tables[0].DefaultView.ToTable();

            if (dx.Tables[0].Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    if (i < dx.Tables[0].Rows.Count)
                    {
                        KPIAssignDetail p = new KPIAssignDetail();
                        p.ID = Convert.ToInt16(dx.Tables[0].Rows[i]["ID"].ToString());
                        p.KPI = new KPIController().GetKPIByID(Convert.ToInt16(dx.Tables[0].Rows[i]["KPIID"].ToString()));
                        p.KPIAssign = new KPIAssignController().GetKPIAssignByID(Convert.ToInt16(dx.Tables[0].Rows[i]["KPIAssignID"].ToString()));
                        p.Employee = new EmployeeController().GetEmployeeByID(dx.Tables[0].Rows[i]["EmployeeID"] == DBNull.Value ? 0 : Convert.ToInt16(dx.Tables[0].Rows[i]["EmployeeID"].ToString()));
                        p.Weight = Convert.ToInt16(dx.Tables[0].Rows[i]["Weight"].ToString());
                        p.UoM = new UoMController().GetUoMByID(dx.Tables[0].Rows[i]["UoMID"] == DBNull.Value ? 0 : Convert.ToInt16(dx.Tables[0].Rows[i]["UoMID"].ToString()));
                        p.UoMDetailMax = new UoMDetailController().GetUoMDetailByID(dx.Tables[0].Rows[i]["UoMDetailIDMax"] == DBNull.Value ? 0 : Convert.ToInt16(dx.Tables[0].Rows[i]["UoMDetailIDMax"].ToString()));
                        p.UoMDetailMin = new UoMDetailController().GetUoMDetailByID(dx.Tables[0].Rows[i]["UoMDetailIDMin"] == DBNull.Value ? 0 : Convert.ToInt16(dx.Tables[0].Rows[i]["UoMDetailIDMin"].ToString()));
                        p.WeightEnableFlag = Convert.ToBoolean(dx.Tables[0].Rows[i]["WeightEnableFlag"].ToString());
                        p.UoMEnableFlag = Convert.ToBoolean(dx.Tables[0].Rows[i]["UoMEnableFlag"].ToString());
                        p.Polarization = dx.Tables[0].Rows[i]["Polarization"].ToString();
                        p.ManualCalculation = Convert.ToInt32(dx.Tables[0].Rows[i]["ManualCalculation"].ToString());
                        p.isActive = Convert.ToBoolean(dx.Tables[0].Rows[i]["isActive"].ToString());
                        p.LastModifiedBy = dx.Tables[0].Rows[i]["LastModifiedBy"].ToString();
                        p.LastModifiedDate = dx.Tables[0].Rows[i]["LastModifiedDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dx.Tables[0].Rows[i]["LastModifiedDate"].ToString());
                        p.CreateBy = dx.Tables[0].Rows[i]["CreateBy"].ToString();
                        p.CreateDate = dx.Tables[0].Rows[i]["CreateDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dx.Tables[0].Rows[i]["CreateDate"].ToString());
                        KPIs.Add(p);
                    }
                    else
                    {
                        break;
                    }

                }
            }

            return KPIs.ToList();
        }

        public decimal GetEmployeeTotalKPIWeight(Employee employees, int year)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "SP_GetEmployeeTotalKPIWeight";
            command.Parameters.Add("@USERID", SqlDbType.VarChar).Value = employees.ID;
            command.Parameters.Add("@YEAR", SqlDbType.VarChar).Value = year;

            return Convert.ToDecimal(GetSingleData(command));
        }

        public decimal GetEmployeeTotalKPIWeightNon(Employee employees, int year)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "SP_GetEmployeeTotalKPIWeight_Non";
            command.Parameters.Add("@USERID", SqlDbType.VarChar).Value = employees.ID;
            command.Parameters.Add("@YEAR", SqlDbType.VarChar).Value = year;

            return Convert.ToDecimal(GetSingleData(command));
        }
    }
}